HW#4 --- last modified March 02 2019 21:17:39..

Solution set.

Due date: Apr 27

Files to be submitted:
  Hw4Problems.pdf
  Recovery.txt

Purpose: To deepen our understanding of logging and database recovery. To learn a few of Oracle's recovery commands

Specification:

Do the following problems out of the book and submit them as Hw4Problems.pdf :

8.2.4 The following is a sequence of undo-log records written by two transactions T and U: <START T>; <T, A, 10>; <START U>; <U,B, 20>; <T,C, 30>; <U,D, 40>; <COMMIT U>; <T,E, 50>; <COMMIT T>. Describe the action of the recovery manager, including changes to both disk and the log, if there is a crash and the last log record to appear on disk is: (a) <START U>. (b) <COMMIT U>. (c) <T, E, 50 >. (d) <COMMIT T>.

8.4.2 For each of the sequence of log records representing the actions of one transaction T, tell all the sequences of events that are legal according to the rules of undo logging, where the events of interest are the writing to disk of the blocks containing database elements, and the blocks of the log containing the the update and commit records. You may assume that log records are written to disk in the order shown; i.e., it is not possible to write one log record to disk while a previous record is not written to disk.

(a) <START T>; <T, A, 10, 11>; <T, B, 20, 21>; <COMMIT T>.

(b) <START T>; <T, A, 10, 21>; <T, B, 20, 21>; <T, C, 30, 31>; <COMMIT T>.

8.4.5 Consider the following sequence of log records: <START S>; <S, A, 60, 61>; <COMMIT S>; <START T>; <T, A, 61, 62>; <START U>; <U, B, 20, 21>; <T, C, 30, 31>; <START V>; <U, D, 40, 41>; <V, F, 70, 71>; <COMMIT U>; --- <T, E, 50, 51>; <COMMIT T>; <V, B, 21, 22>; <COMMIT V>. Suppose that we begin a nonquiescent checkpoint immediately after one of the following log records has been written in (memory): (a) <S, A, 60, 61>. (b) <T, A, 61, 62>. (c) <U, B, 20, 21>. (d)<U, D, 40, 41>. (e) <T, E, 50, 51>.

For each, tell: (i) At what points could the <END CKPT> record be written, and (ii) For each possible point at which a crash could occur, how far back in the log must we look to find all possible incomplete transactions. Consider both the case that the <END CKPT>; records was or was not written prior to the crash.

Non-book work.

Next I would like you to experiment with the recovery system in Oracle. Since you don't have administrative privileges on Oracle at school, you will need to have access to Oracle on some machine at home. Some of you already have installed Oracle server at home. To minimize the number of people people who have to do a fresh install I will let you work in groups of up to four people for this part of the assignment. Note the book problems should still be done individually. Each person in the group should also still submit the groups results in a file Recovery.txt seperately, but should list who he/she worked with. That is, if Bob, Mary and Jane worked together they would each submit the same file Recovery.txt and that file should begin by listing the members of their group. This part of the homework is intended just to see some of the commands Oracle allows so you know that they are available at some point later. It is meant to be straightforward. i.e., Please keep this part of your homework simple.

First, if you are using Oracle 8 you will need to do this homework by using the command svrmgrl and connect internal. Otherwise, if you are using 9 or 10 run sqlplus to connect to your local machine's instance of Oracle. Now keep a spool of all you are doing.

Type: connect sys as sysdba

Type your sysdba password. Now find out the tablespaces for your datafiles. To do this do the following catalog query:

select file_name, tablespace_name from dba_data_files

Take a look at some of these files in a text or hex editor. In the Recovery.txt file tell me what you see. Most of these files are probably binary files. Next, find out which of your control and data files are online or offline by doing:

select name, status from v$datafile;

and

select member, status from v$logfile;

Try taking a tablespace offline with:

alter tablespace tablespace_name offline;

Obviously, change tablespace_name to the name of a real tablespace. Then bring this tablespace back online using the same command but replacing offline with online.

Try forcing a checkpoint by using:

alter system checkpoint;

Finally, try taking a tablespace offline copying the file to another directory and (a) try to take the tablespace back online (b) recover the tablespace using:

recover tablespace tablespace_name;

This shouldn't work because their won't be a file to recover. Copy the file back now and take the tablespace back online. (Bonus) Do searches online to figure out how to get undo/ redo logs working so that you could do a recovery with the recover command.

Point Breakdown